package com.cpi.basilos.silo.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import org.apache.log4j.Logger;

import com.cpi.basilos.corntype.dto.CornTypeDTO;
import com.cpi.basilos.silo.dto.SiloDTO;
import com.cpi.basilos.silo.dto.SiloEntryDTO;
import com.cpi.basilos.silo.dto.SiloEntryMovementsDTO;
import com.cpi.basilos.silo.dto.SiloFiltersDTO;
import com.cpi.basilos.silo.dto.SiloOutputDTO;
import com.cpi.basilos.silo.exception.SiloException;
import com.cs.common.exception.CSException;
import com.cs.common.pool.DBDAO;
import com.cs.common.utils.TimeUtils;

public class SiloDAO extends DBDAO {
	private static org.apache.log4j.Logger logger = Logger.getLogger(SiloDAO.class);
	
	private static final String SQL_GET_ENTRIES = "select convert( datetime, convert(varchar,inicio,111)) as fecha, origen, humedad, furgon, silo, descargas.no_producto, codigo, producto, sum(descarga_kg) as peso, convert( varchar, min(inicio), 120 ) as hora from Descargas inner join productos on descargas.no_producto = productos.no_producto where operacion = 'ENTRADA' and descarga_kg is not null and inicio between ? and ? group by convert(varchar,inicio,111), origen, humedad, furgon, silo, descargas.no_producto, codigo, producto order by min(inicio)";//$NON-NLS-1$
	private static final String SQL_GET_OUTPUTS = "select folio, convert(varchar, inicio, 120), silo, descargas.no_producto, codigo, producto, descarga_kg from descargas inner join productos on descargas.no_producto = productos.no_producto where operacion = 'SALIDA' and descarga_kg is not null and inicio between ? and ?";//$NON-NLS-1$
	private static final String DATE_FORMAT_YYYYMMDD = "yyyy/MM/dd";//$NON-NLS-1$
	private static final String SQL_GET_ENTRIES_BY_DAY = "select furgon, camion, operacion, descargas.Tanque, silo, descarga_kg, des_ref_kg, descargas.No_Producto,  producto, humedad, origen from descargas inner join productos on descargas.No_Producto = productos.No_Producto where convert(varchar,inicio,111) = ?";//$NON-NLS-1$
	public List getEntries(SiloFiltersDTO siloFiltersDTO)throws SiloException{
		List siloEntyDTOs = null;
		SiloEntryDTO siloEntryDTO = null;
		CornTypeDTO cornTypeDTO = null;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String strCompleteDate = null;
		Calendar calCompleteDate = null;
		try{
			siloEntyDTOs = new ArrayList();
			preparedStatement = prepareSQL(SQL_GET_ENTRIES, dsSilos);
			preparedStatement.setString(1, siloFiltersDTO.getInitDate());
			preparedStatement.setString(2, siloFiltersDTO.getEndDate());
			resultSet = executeQuery(preparedStatement);
			while(resultSet.next()){
				siloEntryDTO = new SiloEntryDTO();
				cornTypeDTO = new CornTypeDTO();
				siloEntryDTO.setType(SiloDTO.ENTRY);
				siloEntryDTO.setOrigin(resultSet.getString(2));
				siloEntryDTO.setWetness(resultSet.getDouble(3));
				siloEntryDTO.setFurg(resultSet.getString(4));
				siloEntryDTO.setLocation(resultSet.getString(5));
				cornTypeDTO.setCornTypeId(resultSet.getInt(6));
				cornTypeDTO.setShortName(resultSet.getString(7));
				cornTypeDTO.setDescription(resultSet.getString(8));
				siloEntryDTO.setCornTypeDTO(cornTypeDTO);
				siloEntryDTO.setAmount(resultSet.getDouble(9));
				strCompleteDate = resultSet.getString(10);
				calCompleteDate = Calendar.getInstance();
				calCompleteDate.setTime(TimeUtils.convertStringToDate(strCompleteDate, TimeUtils.DATE_FORMAT_YYYYMMDDHHMM));
				siloEntryDTO.setDate(calCompleteDate);
				siloEntyDTOs.add(siloEntryDTO);
			}
			connection = preparedStatement.getConnection();
		}catch(Exception exception){
			SiloException siloException = null;
			exception.printStackTrace(System.out);
			siloException = new SiloException(exception, CSException.LAYER_DAO, CSException.ACTION_LISTS);
			logger.error(exception);
			throw siloException;
		}finally{
			try{
				closeConnection(connection);
			}catch(Exception exception){}
		}
		return siloEntyDTOs;
	}
	
	public List getOutputs(SiloFiltersDTO siloFiltersDTO)throws SiloException{
		List siloOutputDTOs = null;
		SiloOutputDTO siloOutputDTO = null;
		CornTypeDTO cornTypeDTO = null;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		Calendar date = null;
		String strDate = null;
		try{
			siloOutputDTOs = new ArrayList();
			preparedStatement = prepareSQL(SQL_GET_OUTPUTS, dsSilos);
			preparedStatement.setString(1, siloFiltersDTO.getInitDate());
			preparedStatement.setString(2, siloFiltersDTO.getEndDate());
			resultSet = executeQuery(preparedStatement);
			while(resultSet.next()){
				date = Calendar.getInstance();
				siloOutputDTO = new SiloOutputDTO();
				siloOutputDTO.setType(SiloDTO.OUTPUT);
				siloOutputDTO.setId(resultSet.getInt(1));
				cornTypeDTO = new CornTypeDTO();
				strDate = resultSet.getString(2);
				date.setTime(TimeUtils.convertStringToDate(strDate, TimeUtils.DATE_FORMAT_YYYYMMDDHHMM));
				siloOutputDTO.setDate(date);
				siloOutputDTO.setLocation(resultSet.getString(3));
				cornTypeDTO.setCornTypeId(resultSet.getInt(4));
				cornTypeDTO.setShortName(resultSet.getString(5));
				cornTypeDTO.setDescription(resultSet.getString(6));
				siloOutputDTO.setCornTypeDTO(cornTypeDTO);
				siloOutputDTO.setAmount(resultSet.getDouble(7));
				siloOutputDTOs.add(siloOutputDTO);
			}
			connection = preparedStatement.getConnection();
		}catch(Exception exception){
			SiloException siloException = null;
			exception.printStackTrace(System.out);
			siloException = new SiloException(exception, CSException.LAYER_DAO, CSException.ACTION_LISTS);
			logger.error(exception);
			throw siloException;
		}finally{
			try{
				closeConnection(connection);
			}catch(Exception exception){}
		}
		return siloOutputDTOs;
	}
	
	public List getEntriesByDay(Calendar date)throws SiloException{
		List siloEntryMovementsDTOs = null;
		SiloEntryMovementsDTO siloEntryMovemetsDTO = null;
		CornTypeDTO cornTypeDTO = null;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		SimpleDateFormat simpleDateFormat=null;
		try{
			simpleDateFormat=new SimpleDateFormat(DATE_FORMAT_YYYYMMDD);
			siloEntryMovementsDTOs = new ArrayList();
			preparedStatement = prepareSQL(SQL_GET_ENTRIES_BY_DAY, dsSilos);	
			preparedStatement.setString(1, simpleDateFormat.format(date.getTime()));
			resultSet = executeQuery(preparedStatement);
			while(resultSet.next()){
				siloEntryMovemetsDTO = new SiloEntryMovementsDTO();
				cornTypeDTO = new CornTypeDTO();

				if (resultSet.getString(1)!=null){
					siloEntryMovemetsDTO.setFurgKind(true);
					siloEntryMovemetsDTO.setFurg(resultSet.getString(1));
				}else{
					siloEntryMovemetsDTO.setFurgKind(false);
					if (resultSet.getString(2)!=null){
						siloEntryMovemetsDTO.setFurg(resultSet.getString(2));
					}
				}
				
				siloEntryMovemetsDTO.setOperation(resultSet.getString(3));
				siloEntryMovemetsDTO.setTank(resultSet.getInt(4));
				siloEntryMovemetsDTO.setLocation(resultSet.getString(5));
				
				if (resultSet.getString(6)!=null){
					siloEntryMovemetsDTO.setAmount(resultSet.getDouble(6));
				}else if(resultSet.getString(7)!=null){
					siloEntryMovemetsDTO.setAmount(resultSet.getDouble(7));
				}
				
				cornTypeDTO.setCornTypeId(resultSet.getInt(8));
				siloEntryMovemetsDTO.setCornTypeDTO(cornTypeDTO);
				siloEntryMovemetsDTO.setCornType(resultSet.getString(9));
				siloEntryMovemetsDTO.setWetness(resultSet.getDouble(10));
				siloEntryMovemetsDTO.setOrigin(resultSet.getString(11));
				//siloEntryMovemetsDTO.setOriginAmount(resultSet.getDouble(12));
				
				siloEntryMovementsDTOs.add(siloEntryMovemetsDTO);
				
				
			}
			connection = preparedStatement.getConnection();
		}catch(Exception exception){
			SiloException siloException = null;
			exception.printStackTrace(System.out);
			siloException = new SiloException(exception, CSException.LAYER_DAO, CSException.ACTION_LISTS);
			logger.error(exception);
			throw siloException;
		}finally{
			try{
				closeConnection(connection);
			}catch(Exception exception){}
		}
		return siloEntryMovementsDTOs;
	}
}